"""step5_1_prepare_m_view

目的：
    在物理 SETL 表上追加并填充需要的融合 / 计算列（来自 MDTRT / FEE / 派生逻辑），随后创建视图 m。
    视图 m 只展示 j_drop IS NULL 的有效数据（无效行 j_drop='1' 不出现在视图中），并按 env_structure.xlsx 中的别名(core) 输出列名。
    不做字典汉化 / _MAP 字段。
    不生成 j_refund；保留 j_rand；j_drop= '1' 表示无效，正常为 NULL。

步骤概要：
    1. 读取 env_structure.xlsx (获取 SETL / MDTRT 字段列表及 core 别名)。注意：全都转换为大写
    2. 解析环境变量：
        2.1. 表名 SETL_TBL_NAME / MDTRT_TBL_NAME / FEE_TBL_NAME
        2.2. 医院简称 HSP_ABBR 字典
    3. 判断 MDTRT 表、SETL 表是否有重复数据，如果有则输出例子并报错退出
    4. 为 SETL 表新增所需的 MDTRT 字段（若不存在）与计算字段列
    5. 为 SETL 表更新 MDTRT 记录，先直接按 mdtrt_id 匹配，剩余的按多个字段组合匹配
    6. 计算字段：
        6.1. J_GEND: 性别，基于 GEND 字段映射为 f 或 m
        6.2. J_HSP_LV：医院等级，基于 HSP_ABBR 映射为 3/2/1/0
        6.3. J_ISIN: 是否住院，基于 MED_TYPE 与 IN_DATE0/OUT_DATE0 综合判定
        6.4. IN_DAY / OUT_DAY: 住院起止日期，基于 IN_TIME / OUT_TIME 截断时间部分
        6.5. PERIOD: 住院天数，基于 IN_DAY / OUT_DAY 计算
        6.6. HSP_ABBR: 医院简称，直接从 ENV 表中取得
        6.7. J_YEAROLD: 年龄，基于 BRDY 字段与 IN_DAY 计算
        6.8. J_DAYOLD_IN / J_DAYOLD_OUT: 出生日期与入院/出院日期的天数差
        6.9. J_YEAR: 入院年份，基于 SETL_TIME 截取年份计算
        6.10. J_DROP: 无效标记，初始值均为 NULL
        6.11. J_RAND: 随机数，基于 DBMS_RANDOM.VALUE() 生成
        6.12. J_HAS_B：是否有使用医保内金额，基于 HIFP_PAY > 0 判定
    7. 创建开关列：J_BTN；创建索引：HSP_ABBR, J_ISIN, HSP_LV
    8. 添加主键：SETL_ID
    9. 创建视图 m

运行：
   python STEP5干净表/step5_1_prepare_m_view.py
"""
from __future__ import annotations
import os
import sys
from pathlib import Path
from typing import List, Dict
import pandas as pd
from sqlalchemy import text
from sqlalchemy.engine import Engine
import time

ROOT_DIR = os.path.abspath(os.path.join(os.path.dirname(__file__), '..'))
if ROOT_DIR not in sys.path:
    sys.path.append(ROOT_DIR)

from config import create_db_engine, load_env_tbl_name, load_env_hsp_abbr

# 获得所有表名
SETL_TBL_NAME, MDTRT_TBL_NAME, FEE_TBL_NAME, DX_TBL_NAME, TX_TBL_NAME = load_env_tbl_name()

# 元数据文件与环境变量键名常量
STRUCTURE_FILE = 'env.xlsx'
STRUCTURE_SHEET = 'env_structure'
SETL_ENV = SETL_TBL_NAME
MDTRT_ENV = MDTRT_TBL_NAME
FEE_ENV = FEE_TBL_NAME

# 性别映射（与旧逻辑一致）
GENDER_DECODE_CASE = "DECODE(m.GEND,'1','m','男','m','2','f','女','f',NULL)"

# 医院等级映射 (可按原 step5_1_create_m.py 调整)
HOSPITAL_LEVEL_MAPPING = {
    '3': ['01','02','03','04','12'],
    '2': ['05','06','07','13'],
    '1': ['08','09','10','14'],
    '0': []  # 其他
}

# 时间函数
t0 = time.time()
def elapsed() -> str:
    """返回自脚本启动以来的耗时 (HH:MM:SS)。
    用于将原先的 [INFO]/[START]/[DONE] 等等级标签替换成实时耗时。
    """
    
    # 实际时间，yyy-mm-dd hh:mm:ss
    timeStr = time.strftime("%H:%M:%S", time.localtime())
    
    # 已消耗时间 XX hours XX minutes XX.XX seconds
    delta = int(time.time() - t0)
    if delta < 60:
        return f"{timeStr} (+ {delta} sec)"
    elif delta < 3600:
        m, s = divmod(delta, 60)
        return f"{timeStr} (+ {m} min {s} sec)"
    elif delta < 86400:
        h, rem = divmod(delta, 3600)
        m, s = divmod(rem, 60)
        return f"{timeStr} (+ {h} hour {m} min {s} sec)"
    else:
        d, rem = divmod(delta, 86400)
        h, rem = divmod(rem, 3600)
        m, s = divmod(rem, 60)
        return f"{timeStr} (+ {d} day {h} hour {m} min {s} sec)"

# ------------------------------
#  读取配置与环境
# ------------------------------
def read_env_structure(file_path: str = STRUCTURE_FILE) -> pd.DataFrame:
    # 读取 env_structure.xlsx，返回 DataFrame
    df_raw = pd.read_excel(file_path, sheet_name=STRUCTURE_SHEET)
    needed = ['tbl_name', 'field_code', 'field_name', 'core']
    for c in needed:
            if c not in df_raw.columns:
                    raise ValueError(f'env_structure.xlsx 缺少列: {c}')

    df = df_raw[needed].copy()
    # 标准化
    df['tbl_name'] = df['tbl_name'].astype(str).str.strip().str.upper()
    df['field_code'] = df['field_code'].astype(str).str.strip().str.upper()
    df['core'] = df['core'].fillna('').astype(str).str.strip().str.upper()
    df['field_name'] = df['field_name'].fillna('').astype(str).str.strip()
    print(f"[{elapsed()}] 读取结构表，行数={len(df)}")
    return df

def resolve_table_names() -> tuple[str, str, str]:
    setl = SETL_TBL_NAME
    mdtrt = MDTRT_TBL_NAME
    fee = FEE_TBL_NAME
    print(f"[{elapsed()}] 解析环境变量: {SETL_ENV}={setl}, {MDTRT_ENV}={mdtrt}, {FEE_ENV}={fee}")
    return setl, mdtrt, fee

def resolve_cores(struct_df: pd.DataFrame, tbl_name: str) -> Dict[str, str]:
    # 从结构表中解析指定表的 core -> field_code 映射
    df = struct_df[struct_df['tbl_name'] == tbl_name.upper()]
    core_map = {}
    for _, row in df.iterrows():
        core = row['core']
        field_code = row['field_code']
        if core:
            core_map[core] = field_code
    print(f"[{elapsed()}] 解析 {tbl_name} 核心列映射，共 {len(core_map)} 列")
    return core_map

def get_common_fields(setl_map: Dict[str, str], mdtrt_map: Dict[str, str]) -> List[str]:
    # 获得 SETL 与 MDTRT 共有的核心字段（用于处理更新内容）
    commonFields = []
    for setl_core, setl_field in setl_map.items():
        for mdtrt_core, mdtrt_field in mdtrt_map.items():
            if setl_core == mdtrt_core:
                commonFields.append(setl_field)
                break
    print(f"[{elapsed()}] 解析 SETL 与 MDTRT 共有核心列，共 {len(commonFields)} 列")
    return commonFields

# ------------------------------
#  新增列：确保 SETL 拥有所需 MDTRT 字段 + 派生字段
# ------------------------------
def _column_exists(engine: Engine, table: str, col: str) -> bool:
    sql = text("SELECT 1 FROM user_tab_columns WHERE table_name=:t AND column_name=:c")
    with engine.begin() as conn:
        return conn.execute(sql, {'t': table.upper(), 'c': col.upper()}).fetchone() is not None

def _drop_column(engine: Engine, table: str, col: str):
    ddl = f"ALTER TABLE {table} DROP COLUMN {col}"
    with engine.begin() as conn:
        try:
            conn.execute(text(ddl))
        except Exception as e:
            msg = str(e)
            # ORA-01418: specified index does not exist / ORA-01430: column being dropped does not exist
            if 'ORA-01418' in msg or 'does not exist' in msg or 'ORA-01430' in msg:
                print(f"[{elapsed()}] {col} 不存在 (SKIP)")
            else:
                raise

def _add_column(engine: Engine, table: str, col: str, dtype: str):
    ddl = f"ALTER TABLE {table} ADD ({col} {dtype})"
    with engine.begin() as conn:
        try:
            conn.execute(text(ddl))
        except Exception as e:
            msg = str(e)
            # ORA-01430: column being added already exists in table / ORA-00955 object already exists
            if 'ORA-01430' in msg or 'already exists' in msg or 'ORA-00955' in msg:
                print(f"[{elapsed()}] {col} 已存在 (SKIP)")
            else:
                raise

def _infer_type(col: str) -> str:
    u = col.upper()
    if any(k in u for k in ['TIME','DATE','时间','日期']):
        return 'DATE'
    if any(k in u for k in ['AMT','FEE','SUM','COST','AGE','PERIOD','YEAR','DAY','数量','单价','天数','年龄','总额','费用','金额']):
        return 'NUMBER'
    return 'VARCHAR2(400)'

def fill_columns(
    engine: Engine, setlTblName: str, mdtrtTblName: str,
    setlCoresMap: Dict[str, str], mdtrtCoresMap: Dict[str, str], commonFields: List[str]
):
    # 列出 setlFields
    setlFieldSet = set(setlCoresMap.values())
    # 列出 mdtrtFields
    mdtrtFieldSet = set(mdtrtCoresMap.values())
    # 列出 mdtrtFieldSet 当中不存在于 commonFields 的部分
    mdtrtNeedFieldSet = mdtrtFieldSet - set(commonFields)
    # 这些部分就是需要新增到 SETL 表的字段
    needAddFields = list(mdtrtNeedFieldSet)
    for col in needAddFields:
        if _column_exists(engine, setlTblName, col):
            # 删掉旧列
            _drop_column(engine, setlTblName, col)
        dtype = _infer_type(col)
        _add_column(engine, setlTblName, col, dtype)
        print(f"[{elapsed()}] 列 {col} 已添加，类型 {dtype}")
    print(f"[{elapsed()}] 处理 MDTRT 相关列完成，共 {len(needAddFields)} 列")
    return needAddFields

# ------------------------------
#  判断 mdtrt 表中是否有重复 mdtrt_id，以及重复的 psn_no+trunc(in_time)+trunc(out_time)
#  判断 setl 表中是否有重复的 setl_id
# ------------------------------
def check_mdtrt_duplicates(
    engine: Engine, mdtrtTblName: str, mdtrtCoresMap: Dict[str, str]
):
    # 找到 mdtrt 表中 mdtrt_id 的 core 对应的 field_code
    mdtrtIdCol = mdtrtCoresMap['MDTRT_ID']
    # 找到 mdtrt 表是否有重复的 mdtrtId
    sql = text(f"""
        SELECT {mdtrtIdCol}, COUNT(*) as cnt
        FROM {mdtrtTblName}
        WHERE {mdtrtIdCol} IS NOT NULL AND {mdtrtCoresMap['VALI_FLAG']} = '1'
        GROUP BY {mdtrtIdCol}
        HAVING COUNT(*) > 1
    """)
    with engine.begin() as conn:
        result = conn.execute(sql)
        rows = result.fetchall()
        if rows:
            print(f"[{elapsed()}] MDTRT 表中存在重复的 MDTRT_ID，共 {len(rows)} 条 (ERROR)")
            for r in rows[:10]:  # 仅打印前10条
                print(f"  重复 MDTRT_ID: {r[0]} , COUNT={r[1]}")
            if len(rows) > 10:
                print(f"  ... 还有 {len(rows)-10} 条未显示")
            raise RuntimeError("MDTRT 表中存在重复的 MDTRT_ID，无法继续处理")
        else:
            print(f"[{elapsed()}] MDTRT 表中 MDTRT_ID 唯一性检查通过")

def check_mdtrt_psn_date_duplicates(
    engine: Engine, 
    mdtrtTblName: str, setlTblName: str, 
    mdtrtCoresMap: Dict[str, str], setlCoresMap: Dict[str, str]
):
    # 找到 mdtrtId
    md_mdtrtIdCol = mdtrtCoresMap['MDTRT_ID']
    s_mdtrtIdCol = setlCoresMap['MDTRT_ID']
    # 找到 mdtrt 表中 psn_no, in_time, out_time 的 core 对应的 field_code
    psnNoCol = mdtrtCoresMap['PSN_NO']
    inTimeCol = mdtrtCoresMap['IN_TIME']
    outTimeCol = mdtrtCoresMap['OUT_TIME']
    # 额外再加 med_type, insutype
    medTypeCol = mdtrtCoresMap['MED_TYPE']
    insuTypeCol = mdtrtCoresMap['INSUTYPE']
    # 额外增加 gend, brdy, age, psn_type
    gendCol = mdtrtCoresMap['GEND']
    brdyCol = mdtrtCoresMap['BRDY']
    ageCol = mdtrtCoresMap['AGE']
    psnTypeCol = mdtrtCoresMap['PSN_TYPE']
    # 找到 mdtrt 表是否有重复的 psn_no + trunc(in_time) + trunc(out_time)
    sql = text(f"""
        SELECT 
            {psnNoCol}, 
            TRUNC({inTimeCol}) as in_date, TRUNC({outTimeCol}) as out_date, 
            {medTypeCol}, {insuTypeCol},
            {gendCol}, TRUNC({brdyCol}), {ageCol}, {psnTypeCol},
            COUNT(*) as cnt
        FROM {mdtrtTblName} md
        WHERE {psnNoCol} IS NOT NULL
            AND TRUNC({inTimeCol}) <> TRUNC({outTimeCol})
            AND NOT EXISTS(
                SELECT 1 FROM {setlTblName} s
                WHERE md.{md_mdtrtIdCol} = s.{s_mdtrtIdCol}
            )
            AND md.{mdtrtCoresMap['VALI_FLAG']} = '1'
        GROUP BY 
            {psnNoCol}, TRUNC({inTimeCol}), TRUNC({outTimeCol}), 
            {medTypeCol}, {insuTypeCol}, 
            {gendCol}, TRUNC({brdyCol}), {ageCol}, {psnTypeCol}
        HAVING COUNT(*) > 1
    """)
    with engine.begin() as conn:
        result = conn.execute(sql)
        rows = result.fetchall()
        if rows:
            print(f"[{elapsed()}] MDTRT 表中存在重复的 PSN_NO+IN_TIME+OUT_TIME，共 {len(rows)} 条 (ERROR)")
            for r in rows[:10]:  # 仅打印前10条
                print(f"  重复: PSN_NO={r[0]}, IN_DATE={r[1]}, OUT_DATE={r[2]}, MED_TYPE={r[3]}, INSUTYPE={r[4]}, GEND={r[5]}, BRDY={r[6]}, AGE={r[7]}, PSN_TYPE={r[8]}, COUNT={r[9]}")
            if len(rows) > 10:
                print(f"  ... 还有 {len(rows)-10} 条未显示")
            raise RuntimeError("MDTRT 表中存在重复的 PSN_NO+IN_TIME+OUT_TIME，无法继续处理")
    return psnNoCol, inTimeCol, outTimeCol, medTypeCol, insuTypeCol, gendCol, brdyCol, ageCol, psnTypeCol

def check_setl_duplicates(
    engine: Engine, setlTblName: str, setlCoresMap: Dict[str, str]
):
    # 找到 setl 表中 setl_id 的 core 对应的 field_code
    setlIdCol = setlCoresMap['SETL_ID']
    valiFlagCol = setlCoresMap['VALI_FLAG']
    # 找到 setl 表是否有重复的 setl_id
    sql = text(f"""
        SELECT {setlIdCol}, COUNT(*) as cnt
        FROM {setlTblName}
        WHERE {setlIdCol} IS NOT NULL AND {valiFlagCol} = '1'
        GROUP BY {setlIdCol}
        HAVING COUNT(*) > 1
    """)
    with engine.begin() as conn:
        result = conn.execute(sql)
        rows = result.fetchall()
        if rows:
            print(f"[{elapsed()}] SETL 表中存在重复的 SETL_ID，共 {len(rows)} 条 (ERROR)")
            for r in rows[:10]:  # 仅打印前10条
                print(f"  重复 SETL_ID: {r[0]} , COUNT={r[1]}")
            if len(rows) > 10:
                print(f"  ... 还有 {len(rows)-10} 条未显示")
            raise RuntimeError("SETL 表中存在重复的 SETL_ID，无法继续处理")
        else:
            print(f"[{elapsed()}] SETL 表中 SETL_ID 唯一性检查通过")
    
# ------------------------------
#  分层匹配 MDTRT 记录 (Step0/1)
#  直接从 mdtrt 表中关联并填充，不再去判断 mdtrt 是否唯一
#  一开始先判断一次 mdtrt 表是否有重复
# ------------------------------
def _mdtrt_step0(
    engine: Engine, 
    setlTblName: str, mdtrtTblName: str, 
    setlCoresMap: Dict[str, str], mdtrtCoresMap: Dict[str, str], commonFields: List[str],
):
    # setl 表的 mdtrt_id 与 mdtrt 表的 mdtrt_id 仅仅相等时，直接更新
    md_mdtrtId = mdtrtCoresMap['MDTRT_ID']
    s_mdtrtId = setlCoresMap['MDTRT_ID']

    # 等待更新的字段，即 mdtrtFields
    updateFields = [
        field for core,field in mdtrtCoresMap.items() if core not in ('MDTRT_ID','VALI_FLAG')
    ]
    # 更新值（当 setl 表中该列为空时，才从 mdtrt 表中填充）
    sql = f"""
        MERGE INTO {setlTblName} s
        USING {mdtrtTblName} md
        ON (
            s.{s_mdtrtId} = md.{md_mdtrtId}
            AND s.{setlCoresMap['VALI_FLAG']} = '1'
            AND md.{mdtrtCoresMap['VALI_FLAG']} = '1'
        )
        WHEN MATCHED THEN UPDATE SET
            {', '.join([f"s.{c}=COALESCE(s.{c}, md.{c})" for c in updateFields])}
    """
    with engine.begin() as conn:
        result = conn.execute(text(sql))
        affected = result.rowcount if result.rowcount is not None else 0
    print(f"[{elapsed()}] Step0 MDTRT_ID 直接匹配: {affected} 行更新")
    
def _mdtrt_step1(
    engine: Engine, 
    setlTblName: str, mdtrtTblName: str, 
    setlCoresMap: Dict[str, str], mdtrtCoresMap: Dict[str, str], commonFields: List[str]
):
    # 对剩余未更新（即 mdtrt_id 为空）的记录，按 psn_no, in_date, out_date 匹配
    s_mdtrtId = setlCoresMap['MDTRT_ID']
    md_mdtrtId = mdtrtCoresMap['MDTRT_ID']
    md_psnNo = mdtrtCoresMap['PSN_NO']
    s_psnNo = setlCoresMap['PSN_NO']
    md_inTime = mdtrtCoresMap['IN_TIME']
    s_inDate = setlCoresMap['IN_DATE0']
    md_outTime = mdtrtCoresMap['OUT_TIME']
    s_outDate = setlCoresMap['OUT_DATE0']
    # 再加上 med_type, insutype
    md_medType = mdtrtCoresMap['MED_TYPE']
    s_medType = setlCoresMap['MED_TYPE']
    md_insutype = mdtrtCoresMap['INSUTYPE']
    s_insutype = setlCoresMap['INSUTYPE']
    # 再加上 gend, brdy, age, psn_type
    md_gend = mdtrtCoresMap['GEND']
    s_gend = setlCoresMap['GEND']
    md_brdy = mdtrtCoresMap['BRDY']
    s_brdy = setlCoresMap['BRDY']
    md_age = mdtrtCoresMap['AGE']
    s_age = setlCoresMap['AGE']
    md_psnType = mdtrtCoresMap['PSN_TYPE']
    s_psnType = setlCoresMap['PSN_TYPE']
    # 参与匹配的列 (replace + fill)
    updateFields = [
        field for core,field in mdtrtCoresMap.items() if 
        core not in (
            'MDTRT_ID', 'PSN_NO', 'IN_TIME', 'OUT_TIME', 
            'MED_TYPE', 'INSUTYPE', 'GEND', 'BRDY', 'AGE', 'PSN_TYPE', 'VALI_FLAG'
        )
    ]
    # 更新值（当 setl 表中该列为空时，且 mdtrt_id 为空时，才从 mdtrt 表中填充）    
    with engine.begin() as conn:
        sql = f"""
            MERGE INTO {setlTblName} s
            USING (
                SELECT * FROM {mdtrtTblName} mdd
                WHERE NOT EXISTS(
                    SELECT 1 FROM {setlTblName} ss
                    WHERE mdd.{md_mdtrtId} = ss.{s_mdtrtId}
                )
            ) md
            ON (
                s.{s_mdtrtId} IS NULL
                AND s.{s_psnNo} = md.{md_psnNo}
                AND s.{s_inDate} = TRUNC(md.{md_inTime})
                AND s.{s_outDate} = TRUNC(md.{md_outTime})
                AND TRUNC(md.{md_inTime}) <> TRUNC(md.{md_outTime})
                AND s.{s_medType} = md.{md_medType}
                AND s.{s_insutype} = md.{md_insutype}
                AND s.{s_gend} = md.{md_gend}
                AND TRUNC(s.{s_brdy}) = TRUNC(md.{md_brdy})
                AND s.{s_age} = md.{md_age}
                AND s.{s_psnType} = md.{md_psnType}
                AND s.{setlCoresMap['VALI_FLAG']} = '1'
                AND md.{mdtrtCoresMap['VALI_FLAG']} = '1'
            )
            WHEN MATCHED THEN UPDATE SET
                {', '.join([f"s.{c}=COALESCE(s.{c}, md.{c})" for c in updateFields])}
        """
        result = conn.execute(text(sql))
        affected = result.rowcount if result.rowcount is not None else 0
    print(f"[{elapsed()}] Step1 PSN_NO + 日期 唯一匹配: {affected} 行更新")

def fill_data(
    engine: Engine, 
    setlTblName: str, mdtrtTblName: str,
    setlCoresMap: Dict[str, str], mdtrtCoresMap: Dict[str, str], commonFields: List[str]
):
    # Step0 直接按 mdtrt_id 匹配
    _mdtrt_step0(engine, setlTblName, mdtrtTblName, setlCoresMap, mdtrtCoresMap, commonFields)
    # Step1 按 psn_no + in_date + out_date 匹配
    _mdtrt_step1(engine, setlTblName, mdtrtTblName, setlCoresMap, mdtrtCoresMap, commonFields)

# ------------------------------
#  计算字段
# ------------------------------
def _cal_j_gend(
    engine: Engine, setlTblName: str, setlCoresMap: Dict[str, str],
):
    # 判断是否存在 J_GEND 列，如果有则先删除
    if _column_exists(engine, setlTblName, 'J_GEND'):
        _drop_column(engine, setlTblName, 'J_GEND')
    # 新增 J_GEND 列
    _add_column(engine, setlTblName, 'J_GEND', 'VARCHAR2(10)')
    # 更新 J_GEND 列，通过 core==gend 的列映射
    gendCol = setlCoresMap['GEND']
    sql = text(f"""
        UPDATE {setlTblName}
        SET J_GEND = DECODE({gendCol},'1','m','男','m','2','f','女','f','0','f',NULL)
    """)
    with engine.begin() as conn:
        result = conn.execute(sql)
        affected = result.rowcount if result.rowcount is not None else 0
    print(f"[{elapsed()}] J_GEND 计算完成，共 {affected} 行更新")

def _cal_j_hsp_lv(
    engine: Engine, setlTblName: str, setlCoresMap: Dict[str, str],
):
    # 判断是否存在 J_HSP_LV 列，如果有则先删除
    if _column_exists(engine, setlTblName, 'J_HSP_LV'):
        _drop_column(engine, setlTblName, 'J_HSP_LV')
    # 新增 HSP_LV 列
    _add_column(engine, setlTblName, 'J_HSP_LV', 'VARCHAR2(10)')
    # 更新 HSP_LV 列，通过 core==hsp_lv_raw 的列映射
    hspAbbrCol = setlCoresMap['HSP_LV_RAW']
    case_statements = []
    for level, abbrs in HOSPITAL_LEVEL_MAPPING.items():
        if abbrs:
            in_list = ','.join([f"'{abbr}'" for abbr in abbrs])
            case_statements.append(f"WHEN {hspAbbrCol} IN ({in_list}) THEN '{level}'")
    case_sql = "CASE " + " ".join(case_statements) + " ELSE '0' END"
    sql = text(f"""
        UPDATE {setlTblName}
        SET J_HSP_LV = {case_sql}
    """)
    with engine.begin() as conn:
        result = conn.execute(sql)
        affected = result.rowcount if result.rowcount is not None else 0
    print(f"[{elapsed()}] HSP_LV 计算完成，共 {affected} 行更新")

def _cal_j_isin(
    engine: Engine, setlTblName: str, setlCoresMap: Dict[str, str],
):
    # 判断是否存在 J_ISIN 列，如果有则先删除
    if _column_exists(engine, setlTblName, 'J_ISIN'):
        _drop_column(engine, setlTblName, 'J_ISIN')
    # 新增 J_ISIN 列
    _add_column(engine, setlTblName, 'J_ISIN', 'VARCHAR2(10)')
    # 更新 J_ISIN 列，通过 core==med_type, in_date0, out_date0 的列映射
    medTypeCol = setlCoresMap['MED_TYPE']
    inDateCol = setlCoresMap['IN_DATE0']
    outDateCol = setlCoresMap['OUT_DATE0']
    sql = text(f"""
        UPDATE {setlTblName}
        SET J_ISIN = CASE WHEN SUBSTR({medTypeCol},1,1) = '1' THEN '0'
                          WHEN SUBSTR({medTypeCol},1,1) = '2' THEN '1'
                          WHEN {medTypeCol} = '51' THEN '0'
                          WHEN {medTypeCol} = '52' THEN '1'
                          WHEN TRUNC({inDateCol}) <> TRUNC({outDateCol}) THEN '1'
                          WHEN {medTypeCol} LIKE '%门诊%' THEN '0'
                          WHEN {medTypeCol} LIKE '%住院%' THEN '1'
                          ELSE '0' END
    """)
    with engine.begin() as conn:
        result = conn.execute(sql)
        affected = result.rowcount if result.rowcount is not None else 0
    print(f"[{elapsed()}] J_ISIN 计算完成，共 {affected} 行更新")

def _cal_in_out_day_period(
    engine: Engine, setlTblName: str, setlCoresMap: Dict[str, str], mdtrtCoresMap: Dict[str, str]
):
    # 判断是否存在 IN_DAY, OUT_DAY, PERIOD 列，如果有则先删除
    for col in ['IN_DAY', 'OUT_DAY', 'PERIOD']:
        if _column_exists(engine, setlTblName, col):
            _drop_column(engine, setlTblName, col)
    # 新增 IN_DAY, OUT_DAY, PERIOD 列
    _add_column(engine, setlTblName, 'IN_DAY', 'DATE')
    _add_column(engine, setlTblName, 'OUT_DAY', 'DATE')
    _add_column(engine, setlTblName, 'PERIOD', 'NUMBER')
    # 更新 IN_DAY, OUT_DAY 列，通过 core==in_time, out_time 的列映射
    inTimeCol = mdtrtCoresMap['IN_TIME']
    outTimeCol = mdtrtCoresMap['OUT_TIME']
    sql = text(f"""
        UPDATE {setlTblName}
        SET IN_DAY = TRUNC({inTimeCol}),
            OUT_DAY = TRUNC({outTimeCol}),
            PERIOD = CASE 
                        WHEN TRUNC({outTimeCol}) >= TRUNC({inTimeCol}) THEN 
                            TRUNC({outTimeCol}) - TRUNC({inTimeCol}) + 1
                        ELSE NULL
                     END
    """)
    with engine.begin() as conn:
        result = conn.execute(sql)
        affected = result.rowcount if result.rowcount is not None else 0
    print(f"[{elapsed()}] IN_DAY, OUT_DAY, PERIOD 计算完成，共 {affected} 行更新")

def _get_hsp_abbr() -> str:
    # 动态读取所有 HSPAGGR 环境变量
    hspData = []
    hspListRaw = load_env_hsp_abbr()
    for hspCode, hspAbbr in hspListRaw:
        hspData.append({'hsp_code': hspCode, 'hsp_abbr': hspAbbr})
    return hspData

def _cal_hsp_abbr(
    engine: Engine, setlTblName: str, setlCoresMap: Dict
):
    hspData = _get_hsp_abbr()
    if not hspData:
        raise ValueError("未找到任何 HSPAGGR 环境变量，无法计算 HSP_ABBR")
    # 判断是否存在 HSP_ABBR 列，如果有则先删除
    if _column_exists(engine, setlTblName, 'HSP_ABBR'):
        _drop_column(engine, setlTblName, 'HSP_ABBR')
    # 新增 HSP_ABBR 列
    _add_column(engine, setlTblName, 'HSP_ABBR', 'VARCHAR2(30)')
    # 更新 HSP_ABBR 列，通过环境变量映射
    hspCodeCol = setlCoresMap['HSP_CODE']
    case_statements = []
    for item in hspData:
        code = item['hsp_code']
        abbr = item['hsp_abbr']
        if code:
            case_statements.append(f"WHEN {hspCodeCol} = '{code}' THEN '{abbr}'")
    case_sql = "CASE " + " ".join(case_statements) + " ELSE NULL END"
    sql = text(f"""
        UPDATE {setlTblName}
        SET HSP_ABBR = {case_sql}
    """)
    with engine.begin() as conn:
        result = conn.execute(sql)
        affected = result.rowcount if result.rowcount is not None else 0
    print(f"[{elapsed()}] HSP_ABBR 计算完成，共 {affected} 行更新")

def _cal_j_yearold_dayold(
    engine: Engine, setlTblName: str, setlCoresMap: Dict
):
    # 判断是否存在 J_YEAROLD, J_DAYOLD_IN, J_DAYOLD_OUT 列，如果有则先删除
    for col in ['J_YEAROLD', 'J_DAYOLD_IN', 'J_DAYOLD_OUT']:
        if _column_exists(engine, setlTblName, col):
            _drop_column(engine, setlTblName, col)
    # 新增 J_YEAROLD, J_DAYOLD_IN, J_DAYOLD_OUT 列
    _add_column(engine, setlTblName, 'J_YEAROLD', 'NUMBER')
    _add_column(engine, setlTblName, 'J_DAYOLD_IN', 'NUMBER')
    _add_column(engine, setlTblName, 'J_DAYOLD_OUT', 'NUMBER')
    # 更新 J_YEAROLD, J_DAYOLD_IN, J_DAYOLD_OUT 列，通过 core==brdy, in_day, out_day 的列映射
    brdyCol = setlCoresMap['BRDY']
    inDayCol = setlCoresMap['IN_DATE0']
    outDayCol = setlCoresMap['OUT_DATE0']
    sql = text(f"""
        UPDATE {setlTblName}
        SET J_YEAROLD = CASE 
                            WHEN {brdyCol} IS NOT NULL AND {inDayCol} IS NOT NULL THEN 
                                ( {inDayCol} - {brdyCol} ) / 365.25
                            ELSE NULL
                        END,
            J_DAYOLD_IN = CASE 
                            WHEN {brdyCol} IS NOT NULL AND {inDayCol} IS NOT NULL THEN 
                                {inDayCol} - {brdyCol}
                            ELSE NULL
                          END,
            J_DAYOLD_OUT = CASE 
                            WHEN {brdyCol} IS NOT NULL AND {outDayCol} IS NOT NULL THEN 
                                {inDayCol} - {brdyCol}
                            ELSE NULL
                           END
    """)
    with engine.begin() as conn:
        result = conn.execute(sql)
        affected = result.rowcount if result.rowcount is not None else 0
    print(f"[{elapsed()}] J_YEAROLD, J_DAYOLD_IN, J_DAYOLD_OUT 计算完成，共 {affected} 行更新")

def _cal_j_year(
    engine: Engine, setlTblName: str, setlCoresMap: Dict
):
    # 判断是否存在 J_YEAR 列，如果有则先删除
    if _column_exists(engine, setlTblName, 'J_YEAR'):
        _drop_column(engine, setlTblName, 'J_YEAR')
    # 新增 J_YEAR 列
    _add_column(engine, setlTblName, 'J_YEAR', 'NUMBER')
    # 更新 J_YEAR 列，通过 core==setl_time 的列映射
    setlTimeCol = setlCoresMap['SETL_TIME']
    sql = text(f"""
        UPDATE {setlTblName}
        SET J_YEAR = CASE 
                        WHEN {setlTimeCol} IS NOT NULL THEN 
                            EXTRACT(YEAR FROM {setlTimeCol})
                        ELSE NULL
                     END
    """)
    with engine.begin() as conn:
        result = conn.execute(sql)
        affected = result.rowcount if result.rowcount is not None else 0
    print(f"[{elapsed()}] J_YEAR 计算完成，共 {affected} 行更新")

def _cal_j_drop(
    engine: Engine, setlTblName: str, setlCoresMap: Dict
):
    # 判断是否存在 J_DROP 列，如果有则先删除
    if _column_exists(engine, setlTblName, 'J_DROP'):
        _drop_column(engine, setlTblName, 'J_DROP')
    # 新增 J_DROP 列
    _add_column(engine, setlTblName, 'J_DROP', 'VARCHAR2(1)')
    print(f"[{elapsed()}] J_DROP 计算完成，共 0 行更新")

def _cal_j_rand(
    engine: Engine, setlTblName: str, setlCoresMap: Dict
):
    # 判断是否存在 J_RAND 列，如果有则先删除
    if _column_exists(engine, setlTblName, 'J_RAND'):
        _drop_column(engine, setlTblName, 'J_RAND')
    # 新增 J_RAND 列
    _add_column(engine, setlTblName, 'J_RAND', 'NUMBER')
    # 更新 J_RAND 列，通过 DBMS_RANDOM.VALUE 生成随机数
    sql = text(f"""
        UPDATE {setlTblName}
        SET J_RAND = DBMS_RANDOM.VALUE
    """)
    with engine.begin() as conn:
        result = conn.execute(sql)
        affected = result.rowcount if result.rowcount is not None else 0
    print(f"[{elapsed()}] J_RAND 计算完成，共 {affected} 行更新")

def _cal_j_has_b(engine: Engine, setlTblName: str, setlCoresMap: Dict):
    # 判断是否存在 J_HAS_B 列，如果有则先删除
    if _column_exists(engine, setlTblName, 'J_HAS_B'):
        _drop_column(engine, setlTblName, 'J_HAS_B')
    # 新增 J_HAS_B 列
    _add_column(engine, setlTblName, 'J_HAS_B', 'VARCHAR2(10)')
    # 更新 J_HAS_B 列，通过 core==hifp_pay 的列映射
    hifpPayCol = setlCoresMap['HIFP_PAY']
    sql = text(f"""
        UPDATE {setlTblName}
        SET J_HAS_B = CASE WHEN {hifpPayCol} > 0 THEN '1' ELSE '0' END
    """)
    with engine.begin() as conn:
        result = conn.execute(sql)
        affected = result.rowcount if result.rowcount is not None else 0
    print(f"[{elapsed()}] J_HAS_B 计算完成，共 {affected} 行更新")

def calculate_derived_fields(
    engine: Engine, setlTblName: str, setlCoresMap: Dict, mdtrtCoresMap: Dict
):
    _cal_j_gend(engine, setlTblName, setlCoresMap)
    _cal_j_hsp_lv(engine, setlTblName, setlCoresMap)
    _cal_j_isin(engine, setlTblName, setlCoresMap)
    _cal_in_out_day_period(engine, setlTblName, setlCoresMap, mdtrtCoresMap)
    _cal_hsp_abbr(engine, setlTblName, setlCoresMap)
    _cal_j_yearold_dayold(engine, setlTblName, setlCoresMap)
    _cal_j_year(engine, setlTblName, setlCoresMap)
    _cal_j_drop(engine, setlTblName, setlCoresMap)
    _cal_j_rand(engine, setlTblName, setlCoresMap)
    _cal_j_has_b(engine, setlTblName, setlCoresMap)

# ------------------------------
#  增加开关列、索引、主键与视图
# ------------------------------
def _create_btn_col(
    engine: Engine, 
    setlTblName: str, setlCoresMap: Dict, 
    mdtrtTblName: str, mdtrtCoresMap: Dict,
    feeTblName: str, feeCoresMap: Dict
):
    # 判断是否存在 BTN 列，如果有则先删除
    if _column_exists(engine, setlTblName, 'J_BTN'):
        _drop_column(engine, setlTblName, 'J_BTN')
    # 新增 BTN 列
    _add_column(engine, setlTblName, 'J_BTN', 'VARCHAR2(1)')
    # 更新 BTN 列，全部设为 '1'，'1'则代表是有效的数据
    sql = text(f"""
        UPDATE {setlTblName}
        SET J_BTN = '1'
    """)
    with engine.begin() as conn:
        result = conn.execute(sql)
        affected = result.rowcount if result.rowcount is not None else 0
    print(f"[{elapsed()}] BTN 列初始化完成，共 {affected} 行更新")
    
    # 将 核心列 vali_flag <> '1' 的记录，BTN 设为 '0'
    valiFlagCol = setlCoresMap['VALI_FLAG']
    sql = text(f"""
        UPDATE {setlTblName}
        SET J_BTN = '0'
        WHERE {valiFlagCol} <> '1'
    """)
    with engine.begin() as conn:
        result = conn.execute(sql)
        affected_invalid = result.rowcount if result.rowcount is not None else 0
    print(f"[{elapsed()}] BTN 列更新 VALI_FLAG <> '1' 的记录，共 {affected_invalid} 行更新")
    
    # 将 核心列 insutype 属于特殊类型的记录，BTN 设为 '0'
    insuTypeCol = setlCoresMap['INSUTYPE']
    sql = text(f"""
        UPDATE {setlTblName}
        SET J_BTN = '0'
        WHERE {insuTypeCol} IN ('340','离休人员医疗保障','399','其他特殊人员医疗保障')
    """)
    with engine.begin() as conn:
        result = conn.execute(sql)
        affected_special = result.rowcount if result.rowcount is not None else 0
    print(f"[{elapsed()}] BTN 列更新 INSUTYPE 属于特殊类型的记录，共 {affected_special} 行更新")
    
    # 将 核心列 refd_setl_flag = '1' 或者为 NULL 的记录，BTN 设为 '0'
    refdSetlFlagCol = setlCoresMap['REFD_SETL_FLAG']
    sql = text(f"""
        UPDATE {setlTblName}
        SET J_BTN = '0'
        WHERE {refdSetlFlagCol} = '1'
    """)
    with engine.begin() as conn:
        result = conn.execute(sql)
        affected_refd = result.rowcount if result.rowcount is not None else 0
    print(f"[{elapsed()}] BTN 列更新 REFD_SETL_FLAG = '1' 或 NULL 的记录，共 {affected_refd} 行更新")
    
    # 将 在 mdtrt 表中没有对应记录的 setl 表记录，BTN 设为 '0'
    md_mdtrtIdCol = mdtrtCoresMap['MDTRT_ID']
    s_mdtrtIdCol = setlCoresMap['MDTRT_ID']
    sql = text(f"""
        UPDATE {setlTblName} s
        SET J_BTN = '0'
        WHERE NOT EXISTS (
            SELECT 1 FROM {mdtrtTblName} md
            WHERE s.{s_mdtrtIdCol} = md.{md_mdtrtIdCol}
                AND md.{mdtrtCoresMap['VALI_FLAG']} = '1'
        )
    """)
    
    # 将 在 fee 表中没有对应记录的 setl 表记录，BTN 设为 '0'
    fee_setlIdCol = feeCoresMap['SETL_ID']
    sql_fee = text(f"""
        UPDATE {setlTblName} s
        SET J_BTN = '0'
        WHERE NOT EXISTS (
            SELECT 1 FROM {feeTblName} f
            WHERE s.{setlCoresMap['SETL_ID']} = f.{fee_setlIdCol}
                AND f.{feeCoresMap['VALI_FLAG']} = '1'
        )
    """)
    with engine.begin() as conn:
        result = conn.execute(sql)
        affected_mdtrt = result.rowcount if result.rowcount is not None else 0
        print(f"[{elapsed()}] BTN 列更新在 MDTRT 表中无对应记录的记录，共 {affected_mdtrt} 行更新")
        result_fee = conn.execute(sql_fee)
        affected_fee = result_fee.rowcount if result_fee.rowcount is not None else 0
        print(f"[{elapsed()}] BTN 列更新在 FEE 表中无对应记录的记录，共 {affected_fee} 行更新")
    
    print(f"[{elapsed()}] BTN 列创建完成，共 {affected} 行更新")

def _create_index(
    engine: Engine, setlTblName: str, setlCoresMap: Dict
):
   
    # 需要增加索引的 fields （这些都是派生字段）
    fields = ['J_HSP_LV','HSP_ABBR','J_ISIN','J_BTN','J_YEAR','J_RAND','J_GEND']
    for field in fields:
        with engine.begin() as conn:
            # 判断主键是否已经存在，如果是则删除
            idx_name = f"IDX_{setlTblName}_{field}"
            sql_check = text(f"SELECT 1 FROM user_indexes WHERE index_name = :idx_name AND table_name = :tbl_name")
            exists = conn.execute(sql_check, {'idx_name': idx_name.upper(), 'tbl_name': setlTblName.upper()}).fetchone()
            if exists:
                conn.execute(text(f"DROP INDEX {idx_name}"))
            # 创建主键
            sql = f"CREATE INDEX IDX_{setlTblName}_{field} ON {setlTblName}({field})"
            conn.execute(text(sql))
            print(f"[{elapsed()}] 创建索引 {idx_name} ON {setlTblName}({field})")
    print(f"[{elapsed()}] 创建索引完成，共 {len(fields)} 个索引")

def _create_primary_key(
    engine: Engine, setlTblName: str, setlCoresMap: Dict
):
    setlIdCol = setlCoresMap['SETL_ID']
    with engine.begin() as conn:
        # 判断主键是否已经存在，如果是则删除
        sql = text(f"""
            SELECT 1 FROM user_constraints 
            WHERE constraint_type = 'P' AND table_name = :tbl_name
        """)
        exists = conn.execute(sql, {'tbl_name': setlTblName.upper()}).fetchone()
        # 如果存在则删除
        if exists:
            conn.execute(text(f"ALTER TABLE {setlTblName} DROP PRIMARY KEY"))
        # 创建主键
        sql = f"ALTER TABLE {setlTblName} ADD CONSTRAINT PK_{setlTblName} PRIMARY KEY ({setlIdCol})"
        conn.execute(text(sql))
    print(f"[{elapsed()}] 创建主键完成，共 1 个主键")

def _create_view(
    engine: Engine, setlTblName: str, setlCoresMap: Dict, mdtrtCoresMap: Dict
):
    # 找到 setlFields 与 mdtrtFields 的并集
    setlFieldSet = set(setlCoresMap.values())
    mdtrtFieldSet = set(mdtrtCoresMap.values())
    allFields = list(setlFieldSet.union(mdtrtFieldSet))
    # allFields 还要加上派生字段
    allFields += [
        'J_GEND', 'J_HSP_LV', 'J_ISIN', 'J_HAS_B', 'IN_DAY', 'OUT_DAY', 'PERIOD',
        'HSP_ABBR', 'J_YEAROLD', 'J_DAYOLD_IN', 'J_DAYOLD_OUT', 'J_YEAR', 'J_DROP', 'J_RAND'
    ]
    # 对 allFields 构造 core 字段作为 as 字段
    allFieldData = []
    for field in allFields:
        core = None
        for k,v in setlCoresMap.items():
            if v == field:
                core = k
                break
        if not core:
            for k,v in mdtrtCoresMap.items():
                if v == field:
                    core = k
                    break
        if core:
            allFieldData.append((field, core))
        else:
            allFieldData.append((field, field))
    # 构造视图 SQL
    sql = f"""
        CREATE OR REPLACE VIEW m AS
        SELECT 
            {', '.join([f"{field} AS {core}" for field,core in allFieldData])}
        FROM {setlTblName}
        WHERE J_BTN = '1'
    """
    with engine.begin() as conn:
        conn.execute(text(sql))
    print(f"[{elapsed()}] 创建视图完成，共 1 个视图")


def create_others(
    engine: Engine, setlTblName: str, setlCoresMap: Dict, mdtrtCoresMap: Dict
):
    _create_btn_col(
        engine, 
        setlTblName, setlCoresMap, 
        mdtrtTblName= MDTRT_TBL_NAME,
        mdtrtCoresMap= mdtrtCoresMap,
        feeTblName= FEE_TBL_NAME,
        feeCoresMap= resolve_cores(read_env_structure(STRUCTURE_FILE), 'FEE')
    )
    _create_index(engine, setlTblName, setlCoresMap)
    _create_primary_key(engine, setlTblName, setlCoresMap)
    _create_view(engine, setlTblName, setlCoresMap, mdtrtCoresMap)

# ------------------------------
#  主流程
# ------------------------------
def main():
    # 读取配置与环境
    struct_df = read_env_structure(STRUCTURE_FILE)
    
    setl_table, mdtrt_table, fee_table = resolve_table_names()
    
    # 获得 setl 表与 mdtrt 表的核心列映射
    setlCoresMap = resolve_cores(struct_df, 'SETL')
    mdtrtCoresMap = resolve_cores(struct_df, 'MDTRT')
    
    # 共有核心列
    commonFields = get_common_fields(setlCoresMap, mdtrtCoresMap)

    # 创建数据库连接
    engine = create_db_engine()
    
    # 检查 MDTRT 表中是否有重复的 MDTRT_ID、PSN_NO+IN_TIME+OUT_TIME
    check_mdtrt_duplicates(engine, mdtrt_table, mdtrtCoresMap)
    check_mdtrt_psn_date_duplicates(engine, mdtrt_table, setl_table, mdtrtCoresMap, setlCoresMap)
    check_setl_duplicates(engine, setl_table, setlCoresMap)

    # 为 SETL 表新增所需的 MDTRT 字段，如果是 MDTRT 独有的，则新增相应的列
    print(f'[{elapsed()}] 更新 SETL 的表结构开始')
    fill_columns(engine, setl_table, mdtrt_table, setlCoresMap, mdtrtCoresMap, commonFields)
    print(f'[{elapsed()}] 更新 SETL 的表结构完成')

    # 为 SETL 表更新 MDTRT 记录
    print(f'[{elapsed()}] 更新 SETL 的表数据开始')
    fill_data(engine, setl_table, mdtrt_table, setlCoresMap, mdtrtCoresMap, commonFields)
    print(f'[{elapsed()}] 更新 SETL 的表数据完成')
    
    # 计算派生字段
    print(f'[{elapsed()}] 计算派生字段开始')
    calculate_derived_fields(engine, setl_table, setlCoresMap, mdtrtCoresMap)
    print(f'[{elapsed()}] 计算派生字段完成')
    
    # 创建索引、主键与视图
    print(f'[{elapsed()}] 创建索引与主键开始')
    create_others(engine, setl_table, setlCoresMap, mdtrtCoresMap)
    print(f'[{elapsed()}] 创建索引与主键完成')

if __name__ == '__main__':
    main()
    print(f'[{elapsed()}] 主流程完成')